/** Lachlan Hewson-Doherty (h201384171)
 *  Diploma of Digital and Interactive Games
 *  lachlan_doherty@live.com.au
 *  2013
 */

package cabbage.database.operations;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import cabbage.database.utilities.DatabaseConnection;
import cabbage.model.entities.CheckinBean;
import cabbage.model.entities.CollectibleMasterBean;



public class CollectibleOps {
	
	
	
private DatabaseConnection db;

	
	
	
	
	//private DatabaseConnection db;
	
	
	
	public CollectibleMasterBean getCollectibleMaster(String name) throws SQLException {
		
		 CollectibleMasterBean cmb = null;
		
		
		  db = new DatabaseConnection();
          Connection conn = db.getConnection();
         
          
          PreparedStatement ps = conn.prepareStatement
        		  					("SELECT * FROM collectiblemaster WHERE name=?");
          
          ps.setString
          		(1, name);
          
          ResultSet rs = ps.executeQuery
    							();
          
          while (rs.next()) {
        	  cmb = new CollectibleMasterBean
        			  // Return the stuff in the order of the table
        			  
        			  		   (rs.getInt("collectiblemaster_id"),
        			  				rs.getInt("item_id"),
        			  					rs.getInt("skill_id"),
        			  				 		rs.getInt("faculty_id"),
        			  				 			rs.getInt("collectiblecategory_id"),
        			  				 				rs.getString("name"),
        			  				 					rs.getString("description"),
        			  				 						rs.getString("imagefilename"));
        	  return cmb;
          }
          
          conn.close
          		();
      return cmb;    
	}
          
          
//PreparedStatement ps = conn.prepareStatement
//						("SELECT * FROM checkin WHERE checkin_id=?");
//
//ps.setInt
//(1, checkinid);
//
//ResultSet rs = ps.executeQuery
//				();
//
//while(rs.next())
//{ 				
//cb = new CheckinBean
//			(rs.getInt("checkin_id"),
//				rs.getString("urlstring"),
//					rs.getTimestamp("mytimestamp"));
//}
//
//conn.close
//();
          
          
         
//          PreparedStatement ps = conn.prepareStatement("select c.checkpoint_id, c.checkpointtype_id, c.location_id, l.z from checkpoint c inner join location l on c.location_id=l.location_id where l.z=?");
//          ps.setInt(1, levelZ);
//          ResultSet rs = ps.executeQuery();
//           while(rs.next()) {
//                   System.out.println(rs.getInt("c.checkpoint_id"));
//                   System.out.println(rs.getInt("c.checkpointtype_id"));
//                   System.out.println(rs.getInt("c.location_id"));
//                   System.out.println(rs.getInt("l.z"));
//           }
//           
//           conn.close();
//		
	
	
	
	
	public CollectibleMasterBean getCollectibleMaster(int ID) throws SQLException {
		
		
		return null;
		
	}
	
	
	
	
	public <List> CollectibleMasterBean searchCollectibleMaster(String searchString) throws SQLException {
		
		
		return null;
		
	}
	
	
	
	
	public CollectibleMasterBean getFacultyid(int facultyid) {
		
		return null;
		
	}
	
	
	public CollectibleMasterBean getSkillid(int skillid) {
		
		return null;
		
	}
	
	
	public CollectibleMasterBean getItemid(int itemid) {
		
		return null;
		
	}
	
	
	
	
	
	public boolean checkIfNameExists(String name) throws SQLException {
		
		
		db = new DatabaseConnection
								();

		Connection conn = db.getConnection
										();		

		PreparedStatement ps = conn.prepareStatement
							("SELECT username FROM account WHERE username=?");

		ps.setString
				(1, name);

		ResultSet rs = ps.executeQuery
									();

		if (rs.next()) {	

			conn.close();
			return true;

		}

		conn.close();		
		return false;
		
	}
	
	

	
	public boolean checkIfSkillidExists(int skill_id) throws SQLException {
		
		
		
		db = new DatabaseConnection
								();

		Connection conn = db.getConnection
										();		

		PreparedStatement ps = conn.prepareStatement
							("SELECT skill_id FROM skill WHERE skill_id=?");

		ps.setInt
				(1, skill_id);

		ResultSet rs = ps.executeQuery
									();

		if (rs.next()) {	

			conn.close();
			return true;

		}

		conn.close();		
		return false;
		
	}
	
	
	
	public boolean checkIfFacultyidExists(int faculty_id) throws SQLException {
		
		
		db = new DatabaseConnection
								();

		Connection conn = db.getConnection
										();		

		PreparedStatement ps = conn.prepareStatement
							("SELECT faculty_id FROM faculty WHERE faculty_id=?");

		ps.setInt
				(1, faculty_id);

		ResultSet rs = ps.executeQuery
									();

		if (rs.next()) {	

			conn.close();
			return true;

		}

		conn.close();		
		return false;
		
	}
	
	
	
	public boolean checkIfItemidExists(int item_id) throws SQLException {
		
		
		
		db = new DatabaseConnection
								();

		Connection conn = db.getConnection
										();		

		PreparedStatement ps = conn.prepareStatement
							("SELECT item_id FROM item WHERE item_id=?");

		ps.setInt
				(1, item_id);

		ResultSet rs = ps.executeQuery
									();

		if (rs.next()) {	

			conn.close();
			return true;

		}

		conn.close();		
		return false;
		
	}
	
	
	public boolean checkIfCollectibleMasterExists( int collectiblemaster_id) throws SQLException {
		
		
		
		
		db = new DatabaseConnection
								();

		Connection conn = db.getConnection
										();		

		PreparedStatement ps = conn.prepareStatement
							("SELECT collectiblemaster_id FROM collectiblemaster WHERE collectiblemaster_id=?");

		ps.setInt
				(1, collectiblemaster_id);

		ResultSet rs = ps.executeQuery
									();

		if (rs.next()) {	

			conn.close();
			return true;

		}

		conn.close();		
		return false;
		
	}

	
	
}
